from datetime import datetime, timezone

from sqlalchemy.orm import declared_attr

from applications.extensions import db
from sqlalchemy import func, JSON


# 前台平台模型
# 用户模型
class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='用户ID')
    username = db.Column(db.String(50), unique=True, nullable=True, comment='用户名')
    password = db.Column(db.String(128), nullable=True, comment='密码')
    phone = db.Column(db.String(20), unique=True, nullable=True, comment='手机号')
    avatar = db.Column(db.String(255), default="/static/front/images/def_avatar.jpg", comment='头像')
    description = db.Column(db.String(255), nullable=True, comment='描述')
    enable = db.Column(db.Integer, default=1, comment='启用状态，0为禁用，1为启用')
    birthday = db.Column(db.Date, nullable=True, comment='生日')
    create_at = db.Column(db.DateTime, default=datetime.utcnow, comment='创建时间')
    update_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, comment='更新时间')

    # 微信登录字段
    openid = db.Column(db.String(50), unique=True, nullable=True, comment='微信openid')
    unionid = db.Column(db.String(50), unique=True, nullable=True, comment='微信unionid')
    nickname = db.Column(db.String(50), nullable=True, comment='用户昵称')
    gender = db.Column(db.SmallInteger, nullable=True, comment='性别：0-未知，1-男，2-女')
    country = db.Column(db.String(50), nullable=True, comment='国家')
    province = db.Column(db.String(50), nullable=True, comment='省份')
    city = db.Column(db.String(50), nullable=True, comment='城市')

    # 关系定义
    orders = db.relationship('Order', back_populates='user', cascade='all, delete-orphan')
    comments = db.relationship('Comment', back_populates='user', cascade='all, delete-orphan')
    carts = db.relationship('Cart', backref='user', cascade='all, delete-orphan')
    articles = db.relationship('Article', back_populates='user', cascade='all, delete-orphan')
    art_comments = db.relationship('ArtComment', backref='user', cascade='all, delete-orphan')
    addresses = db.relationship('Address', backref='user', cascade='all, delete-orphan')

    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'nickname': self.nickname,
            'password': '',
            'phone': self.phone,
            'gender': self.gender,
            'avatar': self.avatar,
            'description': self.description,
            'enable': self.enable,
            'birthday': self.birthday.strftime('%Y-%m-%d') if self.birthday else 'Null',
            'create_at': self.create_at.strftime('%Y-%m-%d %H:%M:%S') if self.create_at else 'Null',
        }


# 商品分类模型
class ProductCate(db.Model):
    __tablename__ = 'product_cate'

    # 分类ID，主键
    id = db.Column(db.BigInteger, primary_key=True)
    # 分类名称，不可为空，且具有唯一性
    name = db.Column(db.String(100), nullable=False, unique=True)
    # 分类图片URL
    image = db.Column(db.String(255), nullable=True)
    # 父分类ID，用于实现分类的层级关系
    parent_id = db.Column(db.BigInteger, db.ForeignKey('product_cate.id'), nullable=True)
    # 排序字段，值越小越靠前
    sort_order = db.Column(db.Integer, nullable=False, default=0)
    # 是否展示，True 表示展示，False 表示不展示
    is_displayed = db.Column(db.Boolean, nullable=False, default=True)
    create_time = db.Column(db.DateTime, nullable=False, server_default=func.now())  # 使用数据库函数获取当前时间
    update_time = db.Column(db.DateTime, nullable=False, server_default=func.now(), onupdate=func.now())  # 更新时自动设置为当前时间

    # 定义与子分类的关系，使用递归查询
    @declared_attr
    def children(cls):
        return db.relationship('ProductCate', remote_side=[cls.parent_id],
                               backref=db.backref('parent', remote_side=[cls.id]),
                               lazy='dynamic')

    def to_dict(self):
        """将分类对象转换为字典，方便序列化为JSON格式"""
        return {
            'id': self.id,
            'name': self.name,
            'image': self.image,
            'sort_order': self.sort_order,
            'is_displayed': self.is_displayed,
            'create_time': self.create_time.isoformat(),
            'update_time': self.update_time.isoformat(),
            'children': [child.to_dict() for child in self.children]
        }

    def __repr__(self):
        return f'<ProductCate {self.name}>'


# 商品与标签的多对多关联表
product_tag_association = db.Table('product_tag_association',
                                   db.Column('product_id', db.BigInteger, db.ForeignKey('product.id'),
                                             primary_key=True),
                                   db.Column('tag_id', db.Integer, db.ForeignKey('product_tag.id'), primary_key=True)
                                   )


# 商品标签模型
class ProductTag(db.Model):
    """商品标签模型"""
    __tablename__ = 'product_tag'

    id = db.Column(db.Integer, primary_key=True)  # 标签ID
    name = db.Column(db.String(50), nullable=False)  # 标签名称
    weight = db.Column(db.Integer, default=0, nullable=True, comment='权重，值越大优先级越高')  # 权重
    status = db.Column(db.SmallInteger, default=0, nullable=True, comment='状态：0-禁用，1-启用')  # 状态
    description = db.Column(db.Text, nullable=True)
    color = db.Column(db.String(20), nullable=True, comment='标签颜色')
    type = db.Column(db.SmallInteger, default=1, nullable=False,
                     comment='1-仅商品，2-仅文章，3-商品+文章通用')  # 1-仅商品，2-仅文章，3-商品+文章通用
    sort = db.Column(db.Integer, default=0, nullable=True, comment='排序，值越小越靠前')
    create_time = db.Column(db.DateTime, nullable=False, server_default=func.now())  # 使用数据库函数获取当前时间
    update_time = db.Column(db.DateTime, nullable=False, server_default=func.now(), onupdate=func.now())  # 更新时自动设置为当前时间

    products = db.relationship('Product', secondary='product_tag_association', back_populates='tags')

    def __repr__(self):
        return f'<ProductTag {self.name}>'


# 商品模型
class Product(db.Model):
    __tablename__ = 'product'

    id = db.Column(db.BigInteger, primary_key=True)  # 商品ID
    name = db.Column(db.String(100), nullable=False)  # 商品名称
    description = db.Column(db.Text, nullable=True)  # 商品描述
    price = db.Column(db.Integer, nullable=True)  # 商品价格（单位：分）
    salePrice = db.Column(db.Integer, nullable=True)  # 商品销售价格（单位：分）
    stock = db.Column(db.Integer, nullable=False)  # 库存数量
    category_id = db.Column(db.BigInteger, db.ForeignKey('product_cate.id'), nullable=True)  # 商品分类ID
    status = db.Column(db.SmallInteger, nullable=False, default=0)  # 商品状态：0-下架，1-上架
    main_image = db.Column(db.String(255), nullable=True)  # 商品主图
    images = db.Column(JSON, nullable=True)  # 商品图片列表
    detail_html = db.Column(db.Text, nullable=True)  # 商品详情HTML
    sales = db.Column(db.Integer, nullable=True, default=0)  # 销量
    sort = db.Column(db.Integer, nullable=True, default=0)
    create_time = db.Column(db.DateTime, nullable=True, server_default=func.now())  # 使用数据库函数获取当前时间
    update_time = db.Column(db.DateTime, nullable=True, server_default=func.now(), onupdate=func.now())  # 更新时自动设置为当前时间
    brand = db.Column(db.String(50), nullable=True)  # 品牌

    # 关联关系
    category = db.relationship('ProductCate', backref='products')  # 商品分类
    tags = db.relationship('ProductTag', secondary='product_tag_association', back_populates='products')  # 商品标签
    comments = db.relationship('Comment', back_populates='product', cascade='all, delete-orphan')
    carts = db.relationship('Cart')
    order_items = db.relationship('OrderItem', back_populates='product', cascade='all, delete-orphan')

    def __repr__(self):
        return f'<Product {self.name}>'

    @property
    def image_list(self):
        if self.images is None:
            return []
        return self.images if isinstance(self.images, list) else self.images.split(',')


# 故事分类模型
class ArticleCate(db.Model):
    __tablename__ = 'articlecates'  # 表名

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 分类ID
    name = db.Column(db.String(80), nullable=False)  # 分类名称
    parent_id = db.Column(db.Integer, db.ForeignKey('articlecates.id'), nullable=True, comment='父分类ID') # 父分类ID

    articles = db.relationship('Article', backref='articlecate', lazy=True, cascade='all, delete-orphan')  # 文章关系
    children = db.relationship('ArticleCate', backref=db.backref('parent', remote_side=[id]), lazy=True, cascade='all, delete-orphan') # 子分类关系

    def to_dict(self, recursive=False):
        data = {
            'id': self.id,
            'name': self.name,
            'parent_id': self.parent_id
        }
        if recursive and self.children:
            data['children'] = [child.to_dict(recursive=True) for child in self.children]
        return data

    def to_list_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'parent_id': self.parent_id
        }

    def __repr__(self):
        return f'<ArticleCate {self.name}>'


# 故事文章模型
class Article(db.Model):
    __tablename__ = 'articles'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='文章ID')
    title = db.Column(db.String(120), nullable=False, comment='文章标题')
    content = db.Column(db.Text, nullable=False, comment='文章内容')
    image = db.Column(db.String(255), default="/static/index/images/error_ic_404.png", comment='文章图片')
    status = db.Column(db.SmallInteger, nullable=False, default=0, comment='文章状态，默认为待审核')  # "待发布0","允许发布1""拒绝发布2"，
    audit_reason = db.Column(db.Text, comment='审核理由')
    published_date = db.Column(db.DateTime, default=func.now(), comment='文章的发布日期，默认为当前时间')

    category_id = db.Column(db.Integer, db.ForeignKey('articlecates.id', ondelete='CASCADE'), nullable=False,
                            comment='外键，关联分类ID')
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False,
                        comment='外键，关联用户ID')

    # 关系定义
    comments = db.relationship('ArtComment', back_populates='article', cascade='all, delete-orphan')
    user = db.relationship('User', back_populates='articles')

    def __repr__(self):
        return f'<Article {self.title}>'

    def to_dict(self):
        return {
            'id': self.id,
            'author': self.user.username,
            'status': self.status,
            'image': self.image,
            'audit_reason': self.audit_reason,
            'title': self.title,
            'content': self.content,
            'category_name': self.articlecate.name,
            'category_id': self.category_id,
            'published_date': self.published_date.strftime('%Y-%m-%d %H:%M:%S') if self.published_date else 'N/A'
        }


# 文章评论模型
class ArtComment(db.Model):
    __tablename__ = 'art_comments'  # 统一用下划线
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.Text, nullable=False, comment='评论内容')
    created_at = db.Column(db.DateTime, default=func.now(), comment='评论创建时间')
    article_id = db.Column(db.Integer, db.ForeignKey('articles.id', ondelete='CASCADE'), nullable=False)  # 无 index=True
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)  # 无 index=True

    # 关系定义
    article = db.relationship('Article', back_populates='comments')

    def __repr__(self):
        return f'<ArtComment {self.id} - Article {self.article_id} - User {self.user_id}>'

    def to_dict(self):
        return {
            'id': self.id,
            'article_id': self.article_id,
            'user_id': self.user_id,
            'content': self.content,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S') if self.created_at else 'N/A'
        }


# 商品评论模型
class Comment(db.Model):
    __tablename__ = 'comments'
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.Text, nullable=False, comment='评论内容')
    created_at = db.Column(db.DateTime, default=func.now(), comment='评论创建时间')
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    product_id = db.Column(db.BigInteger, db.ForeignKey('product.id'), nullable=False, comment='外键，关联商品ID')

    # 关系定义
    user = db.relationship('User', back_populates='comments')
    product = db.relationship('Product', back_populates='comments')


# 订单模型
class Order(db.Model):
    __tablename__ = 'orders'
    id = db.Column(db.Integer, primary_key=True)
    order_no = db.Column(db.String(50), unique=True, nullable=False, comment='订单编号')
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False, comment='用户ID')
    total_amount = db.Column(db.Numeric(10, 2), nullable=False, comment='订单总金额')
    status = db.Column(db.SmallInteger, nullable=False, default=0, comment='订单状态：0-待支付，1-已支付，2-已发货，3-已完成，4-已取消')
    admin_status = db.Column(db.String(50), default='待处理', comment='管理员处理状态')
    recipient_name = db.Column(db.String(50), nullable=False, comment='收件人姓名')
    phone = db.Column(db.String(20), nullable=False, comment='收件人手机号')
    address = db.Column(db.String(255), nullable=False, comment='收货地址')
    order_time = db.Column(db.DateTime, nullable=False, default=func.now(), comment='下单时间')
    payment_time = db.Column(db.DateTime, nullable=True, comment='支付时间')

    user = db.relationship('User', back_populates='orders')
    order_items = db.relationship('OrderItem', back_populates='order', cascade='all, delete-orphan')

    def __str__(self):
        return f'Order {self.id} - User {self.user_id} - Status {self.status}'


# 订单详情模型
class OrderItem(db.Model):
    __tablename__ = 'order_items'
    id = db.Column(db.Integer, primary_key=True)
    quantity = db.Column(db.Integer, nullable=False)  # 产品的数量
    price = db.Column(db.Float, nullable=False)  # 产品的单价
    order_id = db.Column(db.Integer, db.ForeignKey('orders.id', ondelete='CASCADE'), nullable=False)
    product_id = db.Column(db.BigInteger, db.ForeignKey('product.id', ondelete='CASCADE'), nullable=False)

    product = db.relationship('Product', back_populates='order_items')
    order = db.relationship('Order', back_populates='order_items')


# 购物车模型
class Cart(db.Model):
    __tablename__ = 'carts'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    product_id = db.Column(db.BigInteger, db.ForeignKey('product.id'), nullable=False)
    quantity = db.Column(db.Integer, nullable=False, default=1)


    def __repr__(self):
        return f'<Cart {self.id} - User {self.user_id} - Product {self.product_id}>'


# 地址模型
class Address(db.Model):
    __tablename__ = 'addresses'
    id = db.Column(db.Integer, primary_key=True)
    recipient_name = db.Column(db.String(50), nullable=False)  # 收件人姓名
    phone = db.Column(db.String(20), nullable=False)  # 电话号码
    address_line = db.Column(db.String(255), nullable=False)  # 地址
    is_default = db.Column(db.Boolean, default=False)  # 是否为默认地址
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)

    def __repr__(self):
        return f'<Address {self.id} - User {self.user_id}>'
